package com.jx.gocomtask.webservice.repository.impl;

import com.jx.gocomtask.webservice.DTO.TaskStatus;
import com.jx.gocomtask.webservice.DTO.TotalLookDTO;
import com.jx.gocomtask.webservice.DTO.ViewTotalDTO;
import com.jx.gocomtask.webservice.DTO.query.MessageQuery;
import com.jx.gocomtask.webservice.DTO.query.PageDTO;
import com.jx.gocomtask.webservice.entity.ExtTaskMessage;
import com.jx.gocomtask.webservice.entity.ExtTaskRelation;
import com.jx.gocomtask.webservice.repository.service.ExtTaskMessageDao;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import javax.management.relation.Relation;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by LWD on 2017/8/11.
 */
@Service
public class ExtTaskMessageDaoImpl implements ExtTaskMessageDao {
    @PersistenceContext
    private EntityManager em;

    @Override
    public PageDTO<ExtTaskMessage> findAllPage(MessageQuery messageQuery) {
        String sql = "select ifnull(deadline,date_sub(date(now()),interval -1 day)) as deadline_time, msg.* from ext_task_message msg  where 1=1";
        StringBuffer sb = new StringBuffer(sql);

        if(null != messageQuery.list_type && "deadline".equals(messageQuery.list_type)){
            if("down".equals(messageQuery.action_type)){
                sb.append(" and (msg.deadline is null or msg.deadline < date_sub(date(now()),interval -1 day)) ");
            }else if("up".equals(messageQuery.action_type)){
                sb.append(" and (msg.deadline is not null " +
                        " and (msg.deadline > date_sub(date(now()),interval -1 day)))");
            }else{

            }
        }

        //标签、内容、姓名模糊查
        if(null != messageQuery.keyword){
            sb.append(" and (msg.label like '%" + messageQuery.keyword + "%'");
            sb.append(" or msg.content like '%" + messageQuery.keyword + "%'");
            sb.append(" or msg.from_user_name like '%" + messageQuery.keyword + "%' ");
            sb.append(" or exists (select user.message_id from ext_task_user user where user.message_id = msg.id and " +
                    "user.task_user_name like '%" + messageQuery.keyword + "%')) ");
        }

        if(null == messageQuery.type) {
            if (null != messageQuery.userId) {
                sb.append(" and (msg.from_user = '" + messageQuery.userId + "'");
                sb.append(" or exists (select user.message_id from ext_task_user user where user.message_id = msg.id and " +
                        "user.task_user = '" + messageQuery.userId + "')) ");
            }
            if(null != messageQuery.statusList){
                sb.append(" and msg.status in(" + messageQuery.statusList + ")");
            }
        }
        if(null != messageQuery.begin_deadline && null != messageQuery.end_deadline){
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String begin_time = sdf.format(messageQuery.begin_deadline) ;
            String end_time = sdf.format(messageQuery.end_deadline);
            sb.append(" and deadline between '"+ begin_time + "' and date_sub(date('"+end_time+"'), interval -1 day)");
        }
        if(null != messageQuery.type){
            if("@wo".equals(messageQuery.type)){
                sb.append(" and exists (select user.message_id from ext_task_user user where user.message_id = msg.id and " +
                    "user.task_user = '" + messageQuery.userId + "') ");
                if(null != messageQuery.statusList){
                    sb.append(" and exists (select user.message_id from ext_task_user user where user.message_id = msg.id and " +
                            "user.status in(" + messageQuery.statusList + "))");
                }
            }
            if("wo@".equals(messageQuery.type)){
                sb.append(" and msg.from_user = '" + messageQuery.userId + "' ");

                if(null != messageQuery.userName){
//                    sb.append(" and (msg.from_user_name = '" + messageQuery.userName + "'");
                    sb.append(" and  (exists (select user.message_id from ext_task_user user where user.message_id = msg.id and " +
                            "user.task_user_name = '" + messageQuery.userName + "'" );
                    if(null != messageQuery.statusList){
                        sb.append(" and user.status in(" + messageQuery.statusList + ")))");
                    }else{
                        sb.append(")) ");
                    }
                }
                if (null != messageQuery.statusList) {
                    sb.append(" and msg.status in(" + messageQuery.statusList + ")");
                }

            }
        }

        // 已删除的不获取
        sb.append(" and msg.status <> " + 5 + "");

        String sort = null;
        if (null == messageQuery.list_type || "create".equals(messageQuery.list_type))
            sort = " order by  msg.create_time desc ";
        else {
            if(null == messageQuery.action_type || "down".equals(messageQuery.action_type))
                sort = " order by deadline_time desc, msg.id desc ";
            else
                sort = " order by deadline_time asc ";
        }
        sb.append(sort);

        Query query = em.createNativeQuery(sb.toString(), ExtTaskMessage.class);
            query.setMaxResults(messageQuery.size);
            query.setFirstResult(messageQuery.page * messageQuery.size);

        List<ExtTaskMessage> list = query.getResultList();
        if(null == list)
            return null;

        Query query1 = em.createNativeQuery(sb.toString(), ExtTaskMessage.class);
        List<ExtTaskMessage> list1 = query1.getResultList();

        PageDTO<ExtTaskMessage> pageDTO = new PageDTO<>();

        if(null != messageQuery && 1 == messageQuery.search_type && StringUtils.isNotEmpty(messageQuery.msgUUID)){
            sql = "select * from ext_task_relation re " +
                    " where re.message_id = (select ms.id from ext_task_message ms where ms.uuid = '"+messageQuery.msgUUID +"')";
            Query queryRelation = em.createNativeQuery(sql, ExtTaskRelation.class);
            List<ExtTaskRelation> relationList = queryRelation.getResultList();
            Map<Long, ExtTaskRelation> relationMap = new HashMap<>();
            if(null != relationList && relationList.size() > 0){
                for(ExtTaskRelation relation : relationList)
                relationMap.put(relation.getRelationMessageId(),relation);
            }
            if(relationMap.size() > 0){
                for(ExtTaskMessage message : list){
                    if(relationMap.containsKey(message.getId()))
                        message.setIsRelation(1);
                }
            }
        }

        pageDTO.elements = list;
        pageDTO.totalElements = list1.size();
        if("up".equals(messageQuery.action_type))
            messageQuery.page++;
        pageDTO.index = messageQuery.page;
        pageDTO.size = messageQuery.size;

        return pageDTO;
    }

    @Override
    public PageDTO<TotalLookDTO> findLookAllPage(MessageQuery messageQuery) {
        String sql = "SELECT " +
                "    task_user, " +
                " max(user) as user, " +
                "    ifnull(MAX( " +
                "        CASE  " +
                "        WHEN  ma.`status`=0 THEN " +
                "            ifnull(num,0) " +
                "        END " +
                "    ),0) AS '0', " +
                "    ifnull(MAX( " +
                "        CASE  " +
                "        WHEN ma.`status`=1 THEN " +
                "            ifnull(num,0) " +
                "        END " +
                "    ),0) AS '1',  " +
                "    ifnull(MAX( " +
                "        CASE  " +
                "        WHEN ma.`status`=2 THEN " +
                "            ifnull(num,0) " +
                "        END " +
                "    ),0) AS '2',  " +
                "    ifnull(MAX( " +
                "        CASE  " +
                "        WHEN ma.`status`=3 THEN " +
                "            num " +
                "        END " +
                "    ),0) AS '3',  " +
                "    ifnull(MAX( " +
                "        CASE  " +
                "        WHEN ma.`status`=4 THEN " +
                "            ifnull(num,0) " +
                "        END " +
                "    ),0) AS '4' " +
                "FROM " +
                "(select max(task_user_name) as user,task_user,`status`,count(1) as num " +
                "from ext_task_user  " +
                "where message_id in(select id from ext_task_message where from_user = '"+messageQuery.userId+"' and " +
                "status in (0,1)) " +
                "group by task_user,`status` " +
                "order by 1,2) ma " +
                "group by task_user " +
                "order by 3 desc ";
        StringBuffer sb = new StringBuffer(sql);
        Query query = em.createNativeQuery(sb.toString());
        query.setMaxResults(messageQuery.size);
        query.setFirstResult(messageQuery.page * messageQuery.size);

        List list = query.getResultList();
        if(null == list)
            return null;
        Iterator iterator = list.iterator();

        List<TotalLookDTO> totalLooks = new LinkedList<>();

        List<TotalLookDTO.StatusNum> statusNums = null;
        TotalLookDTO.StatusNum statusNum = null;
        while(iterator.hasNext()){
            statusNums = new LinkedList<>();
            Object[] row = (Object[])iterator.next();
            TotalLookDTO total = new TotalLookDTO();
            total.userId = row[0].toString();
            total.userName = row[1].toString();

            statusNum = new TotalLookDTO.StatusNum();
            statusNum.status = TaskStatus.START;
            statusNum.num = Long.valueOf(row[2].toString());
            statusNums.add(statusNum);

            statusNum = new TotalLookDTO.StatusNum();
            statusNum.status = TaskStatus.RECEIVED;
            statusNum.num = Long.valueOf(row[3].toString());
            statusNums.add(statusNum);

            statusNum = new TotalLookDTO.StatusNum();
            statusNum.status = TaskStatus.FINISHED;
            statusNum.num = Long.valueOf(row[4].toString());
            statusNums.add(statusNum);

            statusNum = new TotalLookDTO.StatusNum();
            statusNum.status = TaskStatus.REFUSE;
            statusNum.num = Long.valueOf(row[5].toString());
            statusNums.add(statusNum);

            statusNum = new TotalLookDTO.StatusNum();
            statusNum.status = TaskStatus.OVER;
            statusNum.num = Long.valueOf(row[6].toString());
            statusNums.add(statusNum);
            total.statusNums = statusNums;
            totalLooks.add(total);
        }

        Query query1 = em.createNativeQuery(sb.toString());
        List listTotal = query1.getResultList();

        PageDTO<TotalLookDTO> pageDTO = new PageDTO<>();
        pageDTO.elements = totalLooks;
        pageDTO.totalElements = listTotal.size();
        pageDTO.size = messageQuery.size;
        pageDTO.index = messageQuery.page;

        return pageDTO;
    }

    @Override
    public ViewTotalDTO count(MessageQuery messageQuery) {
        String sql = "select " +
                "(select count(1) from ext_task_message where deadline between date(now()) and date_sub(date(now()),interval -1 day) " +
                " and id in (select message_id from ext_task_user where task_user = '" + messageQuery.userId + "'and status in (0,1))) as dayNum,  " +
                "(select count(1) from ext_task_message where deadline between date_sub(date(now()),interval -1 day) and date_sub(date(now()),interval -2 day) " +
                " and id in (select message_id from ext_task_user where task_user = '" + messageQuery.userId + "'and status in (0,1))) as tomorrowNum  " +
                "from ext_task_message " +
                "limit 1;";
        Query query1 = em.createNativeQuery(sql);
        List list1 = query1.getResultList();
        if(null == list1)
            return null;
        Iterator iterator = list1.iterator();


        ViewTotalDTO dto = null;
        while(iterator.hasNext()){
            dto = new ViewTotalDTO();
            Object[] row = (Object[])iterator.next();
            dto.dayNum = Long.valueOf(row[0].toString());
            dto.tomorrowNum = Long.valueOf(row[1].toString());
        }
        return dto;
    }

}
